package com.crm.dao.productionDAO;

import com.crm.pojo.ProductionDetail;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * 产品明细dao层
 *
 */
public interface ProductionDetilsDAO extends CrudRepository<ProductionDetail,Long>, JpaSpecificationExecutor<ProductionDetail> {
    //分页查询所有的产品
    @Query("from ProductionDetail p where p.productionDetailsIsDel='未删除' order by p.pid desc")
    public List<ProductionDetail> queryByPage(Pageable pageable);
    //查询所有的产品
    @Query("from ProductionDetail p where p.productionDetailsIsDel='未删除' order by p.pid desc")
    public List<ProductionDetail> queryAll(Pageable pageable);
    //根据分类id查询产品
    @Query("from ProductionDetail p where p.pcategory = ?1 and p.productionDetailsIsDel='未删除'")
    public List<ProductionDetail> queryPdsByPcategory(Long pcid);
    //模糊查询（根据产品名称和id）全部
    @Query(nativeQuery = true,value = "select * from PRODUCTION_DETAILS pd where (pd.PID like ?1 or pd.PNAME like ?1) and pd.PRODUCTION_DETAILS_IS_DEL='未删除'")
    public List<ProductionDetail> queryBypidOrpnameMh1(String q);
    //模糊查询（根据产品名称和id）未分类
    @Query(nativeQuery = true,value = "select * from PRODUCTION_DETAILS pd where (pd.PID like ?1 or pd.PNAME like ?1) and pd.PRODUCTION_DETAILS_IS_DEL='未删除'and pd.PCATEGORY is null")
    public List<ProductionDetail> queryBypidOrpnameMh2(String q);
    //模糊查询（根据产品名称和id）已分类
    @Query(nativeQuery = true,value = "select * from PRODUCTION_DETAILS pd join PRODUCTIONCATEGORY pc on pd.PCATEGORY=pc.PCID where (pd.PID like ?1 or pd.PNAME like ?1) and pd.PRODUCTION_DETAILS_IS_DEL='未删除'and pc.PCDEPTH=?2")
    public List<ProductionDetail> queryBypidOrpnameMh3(String q, String b);
    /**
     * @author dr5hx
     */
    /**
     * @param name
     * @return 模糊查询产品列表
     */
    @Query("from  ProductionDetail p where concat(p.pname,p.pid)  like ?1")
    public List<ProductionDetail> fuzzyQueryByProductName(String name);
    //查询未删除的产品总数
    @Query(nativeQuery = true,value = "select count(*) from PRODUCTION_DETAILS pd where pd.PRODUCTION_DETAILS_IS_DEL='未删除'")
    public Long queryCount();


}
